package com.navi.loaddata.loadService.impl.opeEqptData;

import com.navi.loaddata.core.etlException.ETLException;
import com.navi.loaddata.core.mysqlEntity.EtlConfD;
import com.navi.loaddata.core.pojo.EqptData;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * LoadOpeEqptDataService
 *
 * @author Matthew
 * @date 2018-05-04
 */
@Service
@Transactional(transactionManager = "secondaryPlatformTransactionManager",
    rollbackFor = {RuntimeException.class, Exception.class})
public class LoadEqptDataService {

  private static final Logger logger = LoggerFactory.getLogger(LoadEqptDataService.class);

  @Autowired
  private GreenPlumRepositoryImpl gpDao;

  public int loadEqptDataMain(EtlConfD etlConfD) throws ETLException {
    int countSize = 0;
    Session session = gpDao.getEntityManagerSession();
    try {
      countSize = loadOpeData(session, etlConfD);
      logger.info("lodEdcData finish");
    } catch (ETLException e) {
      throw new ETLException(e.getMessage());
    }
    return countSize;
  }
  private int loadOpeData(Session session, EtlConfD etlConfD) throws ETLException {
    String startTime = etlConfD.getRunStartTimestamp().toString().substring(0, 19);
    String endTime = etlConfD.getRunEndTimestamp().toString().substring(0, 19);
    QueryRunner runnerGp = JdbcUtil.getQueryRunner_gp();
    QueryRunner runnerMes = JdbcUtil.getQueryRunner_mes();

    String eqptkeySql = "SELECT EQPT_ID FROM M_EQPT_D ORDER BY EQPT_ID";
    String eqptDataSql = "SELECT CASE WHEN MM.SUBFACTORYNAME = 'ARRAY' THEN 1 WHEN MM.SUBFACTORYNAME = 'CF' THEN 2 "
        + "WHEN MM.SUBFACTORYNAME = 'OC' THEN 2 ELSE 0 END AS SHOPKEYFK, MM.SUBFACTORYNAME AS SHOPID, "
        + "MM.MACHINENAME AS EQPTID, MM.DESCRIPTION AS EQPTDSC, CASE WHEN MM.SUBFACTORYNAME = 'ARRAY' THEN 1 "
        + "WHEN MM.SUBFACTORYNAME = 'CF' THEN 2 WHEN MM.SUBFACTORYNAME = 'OC' THEN 2 ELSE 0 END AS UPEQPTKEY, "
        + "MM.SUPERMACHINENAME AS UPEQPTID, MA.LASTEVENTUSER AS EVTUSER, MA.LASTEVENTTIME AS EVTTIMESTAMP, "
        + "MA.LASTEVENTCOMMENT AS EVTNOTE, 'Y' AS MESEQPTFLG FROM MES_MACHINESPEC MM LEFT JOIN MES_MACHINE MA "
        + "ON MA.MACHINENAME = MM.MACHINENAME WHERE MA.UPDATETIME > to_date('" + startTime + "','yyyy/mm/dd HH24:MI:SS')"
        + "AND MA.UPDATETIME <= to_date('" + endTime + "','yyyy/mm/dd HH24:MI:SS') "
        + "AND MM.machinetype IN ('ProductionMachine', 'ProductionResource') "
        + "AND MM.DETAILMACHINETYPE IN ('MAIN' , 'UNIT') ORDER BY MM.MACHINENAME";
    List<EqptData> eqptDatas;
    int count = 0;
    try {
      List<String> eqptkeys = runnerGp.query(eqptkeySql, new ColumnListHandler<String>());
      eqptDatas = runnerMes.query(eqptDataSql, new BeanListHandler<>(EqptData.class));
      for (int i = 0; i < eqptDatas.size(); i++) {
        EqptData eqptData = eqptDatas.get(i);
        if (!eqptkeys.contains(eqptData.getEqptId())) {
          StringBuilder insertSql = new StringBuilder();
          insertSql.append("INSERT INTO M_EQPT_D (SHOP_KEY_FK, SHOP_ID, EQPT_ID, EQPT_DSC, UP_EQPT_KEY, UP_EQPT_ID, EVT_USER, "
              + "EVT_TIMESTAMP, EVT_NOTE, MES_EQPT_FLG, ETL_TIMESTAMP) VALUES (");
          if (null != eqptData.getShopKeyFk()) {
            insertSql.append("'").append(eqptData.getShopKeyFk()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != eqptData.getShopId()) {
            insertSql.append("'").append(eqptData.getShopId()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != eqptData.getEqptId()) {
            insertSql.append("'").append(eqptData.getEqptId()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != eqptData.getEqptDsc()) {
            insertSql.append("'").append(eqptData.getEqptDsc()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != eqptData.getUpEqptKey()) {
            insertSql.append("'").append(eqptData.getUpEqptKey()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != eqptData.getUpEqptId()) {
            insertSql.append("'").append(eqptData.getUpEqptId()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != eqptData.getEvtUser()) {
            insertSql.append("'").append(eqptData.getEvtUser()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != eqptData.getEvtTimestamp()) {
            insertSql.append("'").append(eqptData.getEvtTimestamp()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          if (null != eqptData.getEvtNote()) {
            insertSql.append("'").append(eqptData.getEvtNote()).append("',");
          } else {
            insertSql.append("'").append("").append("',");
          }
          insertSql.append("'").append(eqptData.getMesEqptFlg()).append("',");
          insertSql.append("'").append(Timestamp.valueOf(LocalDateTime.now())).append("'");
          insertSql.append(")");
          SQLQuery queryLoadStep = session.createSQLQuery(insertSql.toString());
          count +=   queryLoadStep.executeUpdate();
          logger.info("已同步机台{}", eqptData.getEqptId());
        }
      }
      logger.info("时间段:[{}]-[{}]共发现{}数据", startTime, endTime, eqptDatas.size());

    } catch (SQLException e) {

      logger.error(etlConfD.getJobName() + "LoadEdcDataService:lodEdcData" + LoggerUtil.stackTraceToString(e));

    }

    return count;

  }


}
